package org.jeecg.modules.lab.syn;

import lombok.extern.slf4j.Slf4j;
import org.jeecg.common.cronJob.MyEnum;
import org.jeecg.common.cronJob.entity.LabCronJob;
import org.springframework.stereotype.Component;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Component
public class SynDataUtil {

    public final static boolean syn_flag = true;
    private StringBuilder insertSQLBuilder = new StringBuilder();


    /**
     * 单表新增数据
     *
     * @param cronJob
     * @param formatDateTime
     */
    public void zuZhuangInsertSql(LabCronJob cronJob, String formatDateTime) {

        String table = cronJob.getTableName();
        String flag = cronJob.getSynFlag();
        String sqlColumn = "";
        List<String> colList = new ArrayList<>();
        try (
                Connection conn = DatabaseConnectionManager.getDataSource().getConnection()
        ) {
            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet tables = metadata.getTables(null, null, table, new String[]{"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                // 获取字段名的元数据信息
                ResultSet columns = metadata.getColumns(null, null, tableName, null);
                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME");
                    colList.add(columnName);
                    if ("".equals(sqlColumn)) {
                        sqlColumn = columnName;
                    } else {
                        sqlColumn += "," + columnName;
                    }
                }
//                System.out.println(sqlColumn);
            }
            // 编写SQL查询语句
            String selectQuery = "SELECT * FROM " + table + " WHERE syn_flag='" + flag + "'"; // 根据业务需求编写相应的查询条件
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(selectQuery)) {
                while (rs.next()) {
                    String valColumn = "";
                    for (String col : colList) {
                        String val = rs.getString(col);
                        if (!"null".equals(val) && val != null && !"syn_flag".equals(col)) {
                            val = "'" + val + "'";
                        } else if (!"null".equals(val) && val != null && "syn_flag".equals(col)) {
                            val = "'" + "Y" + "'";
                        }
                        if ("".equals(valColumn)) {
                            valColumn = val;
                        } else {
                            valColumn += "," + val + "";
                        }
                    }
                    String insertSQL = "INSERT INTO " + table + " (" + sqlColumn + ") VALUES (" + valColumn + ");\n";
                    insertSQLBuilder.append(insertSQL).append("\n");
                }
                if (insertSQLBuilder.length() != 0) {
                    try (FileWriter fileWriter = new FileWriter(new File(MyEnum.creatSql + formatDateTime + ".sql"), true);
                         BufferedWriter writer = new BufferedWriter(fileWriter)) {
                        writer.write(insertSQLBuilder.toString());
                        writer.flush(); // 确保缓冲区的内容写入到文件中
                        insertSQLBuilder.setLength(0);//清除内容
                        //修改数据库中数据的syn_flag为已同步
                        String updateSql = "update " + table + " set syn_flag='Y' WHERE syn_flag='" + flag + "';"; // 根据业务需求编写相应的查询条件
                        try (Statement stmts = conn.createStatement()) {
                            stmts.execute(updateSql);
                        } catch (SQLException e) {
                            System.err.println("Failed to execute SQL script: " + e.getMessage());
                        }
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println("Failed to execute SQL script: " + e.getMessage());
        }
    }


    /**
     * 批量新增
     *
     * @param list
     * @param formatDateTime
     */
    public void batchzuZhuangInsertSql(List<LabCronJob> list, String formatDateTime) {
        try (
                Connection conn = DatabaseConnectionManager.getDataSource().getConnection()
        ) {
            DatabaseMetaData metadata = conn.getMetaData();
            for (LabCronJob cronJob : list) {
                String table = cronJob.getTableName();
                String flag = cronJob.getSynFlag();
                List<String> colList = new ArrayList<>();
                String sqlColumn = "";
                ResultSet tables = metadata.getTables(null, null, table, new String[]{"TABLE"});
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    // 获取字段名的元数据信息
                    try (ResultSet columns = metadata.getColumns(null, null, tableName, null)) {
                        while (columns.next()) {
                            String columnName = columns.getString("COLUMN_NAME");
                            colList.add(columnName);
                            if ("".equals(sqlColumn)) {
                                sqlColumn = columnName;
                            } else {
                                sqlColumn += "," + columnName;
                            }
                        }
                    }
                }
                // 编写SQL查询语句
                String selectQuery = "SELECT * FROM " + table + " WHERE syn_flag='" + flag + "'";
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery(selectQuery)) {
                    while (rs.next()) {
                        String valColumn = "";
                        for (String col : colList) {
                            String val = rs.getString(col);
                            if (!"null".equals(val) && val != null && !"syn_flag".equals(col)) {
                                val = "'" + val + "'";
                            } else if (!"null".equals(val) && val != null && "syn_flag".equals(col)) {
                                val = "'" + "Y" + "'";
                            }
                            if ("".equals(valColumn)) {
                                valColumn = val;
                            } else {
                                valColumn += "," + val + "";
                            }
                        }
                        String insertSQL = "INSERT INTO " + table + " (" + sqlColumn + ") VALUES (" + valColumn + ");\n";
                        //todo:一次性将sql写入文本
//                            writer.write(insertSQL);
                        insertSQLBuilder.append(insertSQL);
                    }
                    if (insertSQLBuilder.length() != 0) {
                        try (FileWriter fileWriter = new FileWriter(new File(MyEnum.creatSql + formatDateTime + ".sql"), true);
                             BufferedWriter writer = new BufferedWriter(fileWriter)) {
                            writer.write(insertSQLBuilder.toString());
                            writer.flush(); // 确保缓冲区的内容写入到文件中
                            insertSQLBuilder.setLength(0);//清除内容
                            //修改数据库中数据的syn_flag为已同步
                            String updateSql = "update " + table + " set syn_flag='Y' WHERE syn_flag='" + flag + "';"; // 根据业务需求编写相应的查询条件
                            try (Statement stmts = conn.createStatement()) {
                                stmts.execute(updateSql);
                            } catch (SQLException e) {
                                System.err.println("Failed to execute SQL script: " + e.getMessage());
                            }
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println("Failed to execute SQL script: " + e.getMessage());
        }
    }

    /**
     * 单表更新
     */
    /**
     * 更新数据
     *
     * @param formatDateTime
     */
    public void zuZhuangUpdateSql(LabCronJob cronJob, String formatDateTime) {
        try (
                Connection conn = DatabaseConnectionManager.getDataSource().getConnection()
        ) {
            DatabaseMetaData metadata = conn.getMetaData();
            String table = cronJob.getTableName();
            String flag = cronJob.getSynFlag();
            List<String> colList = new ArrayList<>();
            ResultSet tables = metadata.getTables(null, null, table, new String[]{"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                // 获取字段名的元数据信息
                try (ResultSet columns = metadata.getColumns(null, null, tableName, null)) {
                    while (columns.next()) {
                        String columnName = columns.getString("COLUMN_NAME");
                        colList.add(columnName);
                    }
                }
            }
            // 编写SQL查询语句
            String selectQuery = "SELECT * FROM " + table + " WHERE syn_flag='" + flag + "'";
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(selectQuery)) {
                while (rs.next()) {
                    String id = "";
                    String valColumn = "";
                    for (String col : colList) {
                        String val = rs.getString(col);
                        if ("id".equals(col)) {
                            id = val;
                        } else {
                            if (!"null".equals(val) && val != null && !"syn_flag".equals(col)) {
                                val = "'" + val + "'";
                            } else if (!"null".equals(val) && val != null && "syn_flag".equals(col)) {
                                val = "'" + "Y" + "'";
                            }
                            if ("".equals(valColumn)) {
                                valColumn = col + "=" + val;
                            } else {
                                valColumn += "," + col + "=" + val;
                            }
                        }
                    }
                    String updateSQL = "UPDATE " + table + " set " + valColumn + " where id='" + id + "';\n";
                    insertSQLBuilder.append(updateSQL);
                }
                if (insertSQLBuilder.length() != 0) {
                    try (FileWriter fileWriter = new FileWriter(new File(MyEnum.creatSql + formatDateTime + ".sql"), true);
                         BufferedWriter writer = new BufferedWriter(fileWriter)) {
                        writer.write(insertSQLBuilder.toString());
                        writer.flush(); // 确保缓冲区的内容写入到文件中
                        insertSQLBuilder.setLength(0);//清除内容
                        //修改数据库中数据的syn_flag为已同步
                        String updateSql = "update " + table + " set syn_flag='Y' WHERE syn_flag='" + flag + "';"; // 根据业务需求编写相应的查询条件
                        try (Statement stmts = conn.createStatement()) {
                            stmts.execute(updateSql);
                        } catch (SQLException e) {
                            System.err.println("Failed to execute SQL script: " + e.getMessage());
                        }
                    } catch (IOException e) {
                        System.err.println(e.getMessage());
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println("Failed to execute SQL script: " + e.getMessage());
        }
    }


    /**
     * 批量更新数据
     *
     * @param formatDateTime
     */
    public void batchzuZhuangUpdateSql(List<LabCronJob> list, String formatDateTime) {
        try (
                Connection conn = DatabaseConnectionManager.getDataSource().getConnection()
        ) {
            DatabaseMetaData metadata = conn.getMetaData();
            for (LabCronJob cronJob : list) {
                String table = cronJob.getTableName();
                String flag = cronJob.getSynFlag();
                List<String> colList = new ArrayList<>();
                ResultSet tables = metadata.getTables(null, null, table, new String[]{"TABLE"});
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    // 获取字段名的元数据信息
                    try (ResultSet columns = metadata.getColumns(null, null, tableName, null)) {
                        while (columns.next()) {
                            String columnName = columns.getString("COLUMN_NAME");
                            colList.add(columnName);
                        }
                    }
                }
                // 编写SQL查询语句
                String selectQuery = "SELECT * FROM " + table + " WHERE syn_flag='" + flag + "'";
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery(selectQuery)) {
                    while (rs.next()) {
                        String id = "";
                        String valColumn = "";
                        for (String col : colList) {
                            String val = rs.getString(col);
                            if ("id".equals(col)) {
                                id = val;
                            } else {
                                if (!"null".equals(val) && val != null && !"syn_flag".equals(col)) {
                                    val = "'" + val + "'";
                                } else if (!"null".equals(val) && val != null && "syn_flag".equals(col)) {
                                    val = "'" + "Y" + "'";
                                }
                                if ("".equals(valColumn)) {
                                    valColumn = col + "=" + val;
                                } else {
                                    valColumn += "," + col + "=" + val;
                                }
                            }
                        }
                        String updateSQL = "UPDATE " + table + " set " + valColumn + " where id='" + id + "';\n";
                        insertSQLBuilder.append(updateSQL);
                    }
                    if (insertSQLBuilder.length() != 0) {
                        try (FileWriter fileWriter = new FileWriter(new File(MyEnum.creatSql + formatDateTime + ".sql"), true);
                             BufferedWriter writer = new BufferedWriter(fileWriter)) {
                            writer.write(insertSQLBuilder.toString());
                            writer.flush(); // 确保缓冲区的内容写入到文件中
                            insertSQLBuilder.setLength(0);//清除内容

                            //修改数据库中数据的syn_flag为已同步
                            String updateSql = "update " + table + " set syn_flag='Y' WHERE syn_flag='" + flag + "';"; // 根据业务需求编写相应的查询条件
                            try (Statement stmts = conn.createStatement()) {
                                stmts.execute(updateSql);
                            } catch (SQLException e) {
                                System.err.println("Failed to execute SQL script: " + e.getMessage());
                            }
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.err.println("Failed to execute SQL script: " + e.getMessage());
        }
    }


    /**
     * 删除数据
     *
     * @param formatDateTime
     */
    public void zuZhuangDeleteSql(LabCronJob cronJob, String formatDateTime) {
        String table = cronJob.getTableName();
        //删除条件
        String askTo = cronJob.getAskTo();
        String deleteSQL = "DELETE FROM " + table + " WHERE " + askTo + ";\n";
        // 写入SQL脚本文件
        try (FileWriter fileWriter = new FileWriter(new File(MyEnum.creatSql + formatDateTime + ".sql"), true);
             BufferedWriter writer = new BufferedWriter(fileWriter)) {
            writer.write(deleteSQL);
            writer.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 执行同步sql脚本
     */
//    public void batchExecuteSql() {
//        String url = "jdbc:mysql://localhost:3306/lab?characterEncoding=utf-8&allowMultiQueries=true";
//        String username = "root";
//        String password = "123456";
//        String scriptsFolderPath = "D:/opt/labSql";
//        String backupFolderPath = "D:/opt/backupSql";
//        log.info("开始执行脚本：" + new Date());
//        File folder = new File(scriptsFolderPath);
//        File[] scriptFiles = folder.listFiles((dir, name) -> name.toLowerCase().endsWith(".sql"));
//
//        if (scriptFiles != null) {
//            try (
//                    Connection conn = DriverManager.getConnection(url, username, password)
//            ) {
//                conn.setAutoCommit(false); // 关闭自动提交，启用手动提交事务
//                for (File scriptFile : scriptFiles) {
//                    StringBuilder sqlBuilder = new StringBuilder();
//                    try (BufferedReader reader = new BufferedReader(new FileReader(scriptFile))) {
//                        String line;
//                        while ((line = reader.readLine()) != null) {
//                            sqlBuilder.append(line);
//                            sqlBuilder.append("\n");
//                        }
//                    }
//                    try (Statement stmt = conn.createStatement()) {
//                        stmt.execute(sqlBuilder.toString());
//                        System.out.println("SQL script " + scriptFile.getName() + " executed successfully");
//                        // 复制到备份文件夹
//                        Path sourcePath = scriptFile.toPath();
//                        Path destPath = Paths.get(backupFolderPath, scriptFile.getName());
//                        Files.copy(sourcePath, destPath, StandardCopyOption.REPLACE_EXISTING);
//                        scriptFile.delete();
//                    } catch (SQLException e) {
//                        conn.rollback(); // 回滚事务
//                        System.err.println("Failed to execute SQL script " + scriptFile.getName() + ": " + e.getMessage());
//                    }
//                }
//                conn.commit(); // 提交事务
//                log.info("脚本执行完成：" + new Date());
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        } else {
//            System.out.println("No SQL script files found in the specified folder");
//        }

}
